Computerized Investing - Spreadsheet Collection

home *** CD-ROM | disk | FTP | other *** search

/ Computerized Investing - Spreadsheet Collection / Spreadsheet Collection.iso / pc / ibm11 / bondmgmt.doc < prev next >

Wrap

Text File | 1991-07-12 | 20.7 KB | 342 lines

A Bond Portfolio Worksheet By Fred Shipley, Ph.D. Computerized Investing, January/February 1989 To effectively manage a bond portfolio an investor must keep track of the current return, any current or anticipated changes in value and the riskiness of the portfolio. In this article we provide a template for performing these calculations and updating portfolio values over time. For an investor considering the management or performance evaluation of a bond portfolio, one of the first aspects that must be analyzed is the current income the portfolio provides. Since most investors are, and should be, looking to the bond part of their portfolio to provide a steady income stream, it is important to know the yield this portfolio provides. As with any individual bond, we want to know both the current yield and the yield to maturity. Certainly an investor also needs to know the change in the value of his or her portfolio over time. In our simple spreadsheet, we allow you to determine that change from some initial value--either your initial cost, or as of some specified past date. By updating the spreadsheet on a regular basis you can track your portfolio's performance over time. Finally, for planning and risk management purposes, an investor must know the effect of changes in interest rates on portfolio values. The spreadsheet includes a summary indication of those effects, a means of modeling the effects of changes in market rates, and a screen for individually changing assessments of yields on each individual bond in the portfolio. Bond Portfolio Summary Screen Figure 1 shows the overall portfolio summary. It is calculated from data on each individual bond's value and cost, their coupon rates and maturities. As a result of this effort, you get the portfolio's change in value over cost, its current income yield and approximate yield to maturity, the portfolio duration, and an estimate of the effect on value for a 1% change in interest rates. Finally, by entering new estimates of market rates for each bond you hold, the program will calculate the new market value, yield to maturity and the percentage change in portfolio value that would result from these interest rate changes. Alternatively, you can enter a summary overall market yield for the entire portfolio and get the same information. Figure 1 Bond Portfolio Management Worksheet A B C D E 1 Current Portfolio Holdings -- Summary Information 2 3 Day Month Year 4 Current Date: 26 10 1988 5 6 Initial Portfolio Cost: $46,963.75 7 Current Portfolio Market Value: $47,801.25 8 Change in Value Since Purchase: $837.50 9 ========= 10 Current Portfolio Yield: 8.206% 11 Approximate Portfolio YTM: 9.925% 12 Portfolio Duration (years): 6.80 13 Effect of 1% Increase in Yield: -6.19% 14 15 Effects of Revised Yield Assumptions 16 New Estimated Portfolio YTM: 10.171% 17 New Portfolio Value: $46,954.86 18 %age Change in Portfolio Value: -1.77% 19 Change in Value Since Purchase: ($8.89) 20 TYPE PgDn to Enter Data ========= Remember that current yield only relates the income from a bond to its current price. It does not consider the effects of value appreciation or decline over time as a result of decreasing maturity. This yield measure does not adequately reflect the true return on your portfolio. What it does indicate is the current return per dollar of current market value. The yield to maturity is a better measure of total return, but there are several important assumptions embedded in that concept. First, there is the obvious assumption that you actually hold the bond until maturity. That may not be too bad a presumption. Second, we assume that any cash flows the portfolio generates (periodic interest income) are reinvested at the yield to maturity. This may be stretching things. Many investors consume the current interest payments. The effect of spending these cash flows is to reduce your realized rate of return. Third, the spreadsheet only calculates the approximate yield to maturity. We have done this simply to reduce the size of the program. To calculate the true yield, you would have to create a column (or row) of cash flows for each individual bond. For many investors this would result in an unwieldy mess. For the bonds used to illustrate the spreadsheet the true yield to maturity is 9.955%, while the approximate yield to maturity is 9.925%, a difference of 3 basis points (3/100s of 1%). Using Duration in Portfolio Management To estimate the effect of changing interest rates on the value of the portfolio, we calculate the average portfolio duration. Duration is a present-value-weighted-average time to maturity and is related to the price sensitivity of a bond or bond portfolio. The portfolio duration is just the weighted average of the duration of each individual bond. You must weight each bond's duration by its percentage composition in your portfolio. This percentage is based on current market values. For any bond, its percentage composition (Wb) of the portfolio is: Market Value of Holdings of Bond b Wb = -------------------------------------- Total Market Value of Portfolio These percentages are calculated in the spreadsheet. If you look at Figures 2 and 3, you will see that calculation. For example, the IBM 10.25s of 1995 have a value of $21,075.00 and the total value of the portfolio is $47,801.25 so the IBM bonds are 44% of the portfolio. Figure 2 Data Input Section A B C D E F G 22 Data Input Section -- Enter Individual Bond Information 23 24 Years Initial Units 25 Maturity to Cost Owned 26 Issuer Rating Coupon Date Maturity (% of Face) ($000s) 27 ----------------------------------------------------------------------- 28 A T & T AA 7.000% 02/15/2001 12.31 79.000 5 29 A T & T AA 8.625% 04/01/2026 37.43 93.500 10 30 Chrysler BBB 12.000% 11/15/2015 104.625 3 31 DuPont AA 6.000% 12/01/2001 78.000 5 32 I.B.M. AAA 10.250% 10/15/95 102.875 20 33 Turner Broadcasting B 0.000% 03/15/92 60.500 10 34 --------------------------------------------------- 35 Portfolio Totals: $46,963.75 36 Weighted Average Maturity: 05/30/2003 37 Number of Years to Maturity: 14.6 yrs. Figure 3 Current Portfolio Holdings H I J K L M N 24 Current 25 Price Market % of Current Approx. Duration 26 (% of Face) Value Holdgs Yield Income Y.T.M. (Years) 27 --------------------------------------------------------------------- 28 84.000 $4,200.00 8.79% 8.333% $350.00 9.182% 8.06 29 91.125 $9,112.50 19.06% 9.465% $862.50 9.361% 10.89 30 107.125 $3,213.75 6.72% 11.202% $360.00 11.255% 8.84 31 75.000 $3,750.00 7.84% 8.000% $300.00 9.304% 8.59 32 105.375 $21,075.00 44.09% 9.727% $2,050.00 9.182% 5.20 33 64.500 $6,450.00 13.49% 0.000% $0.00 13.330% 3.38 34 --------------------------------------------------------------------- 35 $47,801.25 100.00% 8.206% $3,922.50 9.925% 6.80 36 ========================================================== These percentage weights allow us to determine the duration of the entire portfolio. For our example portfolio, the duration is 6.8 years, or about 6 years and 10 months. In contrast, the average maturity of the portfolio is 14.6 years. The numbers in Figures 2 and 3 illustrate several important features of duration. First, as we just saw, duration is always less than or equal to time to maturity. For zero coupon issues, such as the Turner Broadcasting notes, the duration is exactly the time to maturity. For all other bonds, the duration will be less than time to maturity. Second, the duration of a portfolio is sensitive to portfolio composition. Nearly half of the value of this portfolio is in the IBM bonds, which are relatively short term. That holding causes the duration of the portfolio to be relatively short. Increasing the percentage of holdings in the AT&T debentures that are due in 2026 would significantly increase the duration of the portfolio. Duration allows us to estimate the effects of changes in interest rates on the value of our bond holdings. The longer the duration of our portfolio, the greater the change in value when interest rates change. (Another discussion of duration appeared in the September/October, 1988 issue of CI. See pages 7 and 8, especially.) Remember that the value of a bond or portfolio of bonds does not change symmetrically with symmetric changes in interest rates. That is, if the average yield to maturity for the bonds in our portfolio were to increase by 1 percentage point, the value of the portfolio would decrease. This decrease in value is less than the increase in value that would occur, if the average yield to maturity on the portfolio were to decrease by 1 percentage point. Nevertheless, duration allows us to get an idea of the impact of changes in the levels of market interest rates. As Figure 1 shows, an increase in rates of 1% would decrease the value of the portfolio by about 6.19%. Of course, we would be able to invest any new funds at higher returns, so some of this loss would be offset. In fact, duration allows us to balance out the effects of lower portfolio values and higher reinvestment opportunities, if we so desire. Balancing the Effects of Higher Yields and Lower Portfolio Values Interest rate changes have two effects on bond portfolios. Suppose, for example, that market interest rates increase. When rates increase, the values of existing bonds decline. With higher rates, however, new investment, or reinvestment of future cash flows, will earn a higher return. With the exception of the zero coupon issue, all of our portfolio is generating cash flows through interest income. These cash flows are thus more valuable when interest rates rise. An important question for an investor is how long must the cash flows occur in order for the higher reinvestment rate to balance out the effect of the lower portfolio value. Can an investor structure a portfolio so as to minimize changes in total return? You might think that the best way to accomplish the goal of decreasing variations in bond returns is to simply hold the bonds in your portfolio until they mature. This will not work. While you will get the face value of your investment back if you do so, you are, in fact, holding your bonds too long. Selling before the time to duration results in net price risk; selling after duration results in net reinvestment rate risk. For interest rate increases, net price risk simply means that the potential effect of decreasing portfolio value outweighs the effect of increasing reinvestment return. Net reinvestment rate risk means that the effect of greater reinvestment opportunities is outweighs the effect of decreasing portfolio value. The appropriate way to minimize variation in bond returns is to set the duration of a bond portfolio equal to your anticipated holding period. By doing so, the effects of value changes due to interest rate variations will be offset by the greater returns due to better reinvestment opportunities. Strictly speaking, whenever interest rates change, so does the duration of the portfolio. Figure 4 shows this. Because of the increase in the yields to maturity of the bonds in the portfolio, the duration of the portfolio has decreased to 6.64 years. In order to maintain a duration equal to the years remaining in your holding period, you must rebalance your portfolio. That is, you must reallocate funds among the various bonds so that the duration is adjusted to counteract the effects of changing interest rates. For most individuals, this is simply too costly. If interest rate changes are not too severe, the penalty from failing to adjust your portfolio is probably something you can ignore. Most institutions rebalance only on an annual basis. Figure 4 Revised Market Yield Assumptions O P Q R S T U 25 Revised 26 Changed New Value Current Total % of Duration 26 Y.T.M. (% of Face) Income Yield Value Holdgs in Years 27 ---------------------------------------------------------------------- 28 9.800% 80.231 $350.00 8.725% $4,011.53 8.54% 7.94 29 9.800% 88.344 $862.50 9.763% $8,834.41 18.81% 10.51 30 12.500% 96.150 $360.00 12.480% $2,884.51 6.14% 8.21 31 9.400% 74.690 $300.00 8.033% $3,734.49 7.95% 8.57 32 9.200% 105.315 $2,050.00 9.733% $21,062.93 44.86% 5.20 33 13.500% 64.270 $0.00 0.000% $6,426.98 13.69% 3.38 34 ---------------------------------------------------------------------- 35 10.171% $3,922.50 8.354% $46,954.86 100.00% 6.64 36 ======== =================================================== What understanding duration allows you to do is structure certain parts of your portfolio to realize certain goals. For example, suppose you are planning on several future major cash outlays at different times. Perhaps you plan to retire in 10 years and buy a condominium in Florida; then in another 3 years (13 years in the future) you want to make a round-the-world cruise; finally after 20 years you want to present your grandchildren with enough cash to get them through an expensive university. If you structured your portfolio so that you had enough bonds with a duration of 10 years to provide your down payment on the condominium, enough with a duration of 13 years to go on your cruise, and a sufficient sum with a duration of 20 years to endow your grandchildren's education, you would achieve your objectives with little variation in total return on your portfolio. This allows you to project quite accurately the return you need to provide the necessary cash. Setting up the Input Data The input screen is shown in Figure 2. While we are using only six bonds to illustrate the process, you may enter as many as you want. Simply copy the necessary formulas down as many rows as you need. The basic data required are the issuer, the bond rating, the coupon rate, the maturity date, the initial cost and the number of bonds owned. The cost should be entered the way price quotes are normally given, as a percentage of face value. Finally, to simplify some of the duration formulas, which are quite complex, we have calculated some preliminary data. This appear in Figure 5. Figure 5 Miscellaneous Calculations V W Z Y Z AA AB AC AD AE AF 23 [] REVISED DATA CALCULATIONS [] ORIGINAL DATA CALCULATIONS [] 24 [] @ Revised YTMs [] @ ytm = approx. ytm [] 25 []Weighted Weighted Annuity P. V. []Weighted Weighted Annuity P. V. [] 26 []Duration Y.T.M. Factor Factor [] Y.T.M. Duration Factor Factor [] 27 []--------------------------------[]----------------------------------[] 28 [] 0.679 0.008 14.121 0.308 [] 0.008 0.708 14.566 0.331 [] 29 [] 1.977 0.018 19.840 0.028 [] 0.018 2.076 20.670 0.033 [] 30 [] 0.504 0.008 15.398 0.038 [] 0.008 0.594 16.851 0.052 [] 31 [] 0.681 0.007 14.888 0.300 [] 0.007 0.673 14.964 0.304 [] 32 [] 2.331 0.041 10.123 0.534 [] 0.040 2.292 10.129 0.535 [] 33 [] 0.463 0.018 5.293 0.643 [] 0.018 0.457 5.309 0.646 [] 34 []--------------------------------[]----------------------------------[] 35 [] [] [] 36 [] [] [] The only difficult entry is the maturity date. To enter that we must use of the spreadsheet's date functions. The @date function takes a given year, month and day and converts them into something Lotus can understand. 1-2-3 keeps track of time by counting the days since December 31, 1899 and can go to December 31, 2099. Years are represented serially from 1900. For example, 1988 is simply 88, but 2011 would be 111. The form of the @date function is: @date(year number,month number,day number)<P> Our first AT&T bond matures on February 15, 2001. The formula that appears in cell D28 is @date(101,2,15). Lotus can also deal with formulas or cell references in the @date function. For example, one of our inputs is the current date in cells B4, C4 and D4. Since we have entered 1988 in cell D5, when we want to show that reference, we use: @date($D$4-1900,$C$4,$B$4)<P> Subtracting 1900 from 1988 gives us the year 88, which is what 1-2-3 expects in the formula. The dollar signs ($s) ensure that the cell references do not change when we copy the formula. For users with Release 2 of 1-2-3 or a compatible program, you can simply use the @now function to return the current date as stored in your PC. However, if you do not set this date each time you start your computer, or have the date set automatically, then all the calculations will be off. Since 1-2-3 determines every date serially, you must then format the cell where the date function appears to translate that serial number and display it as a date. Cells D28 through D33 are formatted using the / Range Format Date command, with the first date format. Examining the Effects of Changes in Market Interest Rates Figure 4 shows the results of changes in your estimates of market yields. The worksheet is designed so you can enter either a new yield for each individual bond in your portfolio, or you can simply enter a single, overall portfolio yield. In Figure 4 we have entered new data for each individual bond. The results of these changes are summarized in the lower part of the summary screen, which is shown in Figure 1. The average portfolio yield to maturity is taken from cell M35 and appears in cell D11. The new portfolio value and the percentage and dollar change from the current value are shown immediately below D11. As you can see, we have assumed that yields on the bonds have increased. This results in a decline in portfolio value as well as a slight decline in the portfolio's duration. As a result of the decrease in the market value of the portfolio, the current yield has increased. This increase reflects the improvement in reinvestment opportunities as a consequence of the interest rate rise. What this spreadsheet allows you to do is understand the current position of your fixed income portfolio and plan for anticipated changes. With this information and an understanding of how interest rate changes affect your bond holdings you will be in a better position to make careful financial plans. (c) Copyright 1989 by the American Association of Individual Investors